ASC2MDB version : Oct / 93 =========================== ASC2MDB CIS filename: ASC2DB.ZIP (because of 6 character limit) CONTAINS SOURCE CODE FOR VB3 PROFESSIONAL A VB3 framework for loading .MDB files from various format ASCII sequential files. OPTIMIZING is not the goal, rather - get each application running with minimum effort - and keep the framework re-usable. Done to solve a few immediate problems ... it changes a little every time I use it, hope it's useful already... -- Richard Curzon [71371,2521] copyright 1993 by Richard Curzon May be used freely as a personal utility but may not be resold without express permission from the author May be copied for personal use under these terms Please include credits where due. No fee is charged for use under these terms. However, if this program inspires you to do so, sending a gift of $5.00 or any amount in a postal money order would be appreciated. Richard Curzon 9 Manser Court Ajax, Ontario CANADA L1T 3C5. HOW TO UNPACK THE ZIP: ---------------------- pkunzip -d asc2mdb.zip c:\vb The "-d" switch will unpack the files into subdirectories c:\vb\ASC2MDB and c:\vb\ASC2MDB\SPECIF explained later. It will also create the subdirectories. QUICK START: ------------ a) try the sample file in C:\VB\ASC2MDB\SPECIF\LSCMAKER.MAK - first UNZIP the files as per above - load LSCMAKER into VB3 professional - RUN LSCMAKER - Push the START button on the form to make the database b) try runtime change of input file: - RUN LSCMAKER.BAS - use the Edit menu to change to Replace Mode 3 - use the Edit menu to change the input file to LSCMAKER.RPL - push START to update selected records in the .MDB c) check out BLK_DEL sample app for crunching a COBOL formatted report file directly into an .MDB file. Hint: all the tricks are in the PassFilter function. d) one way to start your own application: - copy LSCMAKER.BAS to myproj.bas, in the same directory - copy LSCMAKER.MAK to myproj.mak, in the same directory - edit MYPROJ.MAK, to point to your MYPROJ.BAS file instead of LSCMAKER.BAS - edit LSCMAKER.BAS (here's where the work is) 1) change the 5 globals in (declarations) for your case ' the raw ascii input file: Global Const gcDefInputName = "\vb\asc2mdb\specif\lscmaker.asc" ' the .mdb destination: Global Const gcDefDbName = "\vb\asc2mdb\lscmaker.mdb" ' Run log recreated on each run (statistics & errors). Global Const gcErrFileName = "\vb\asc2mdb\lsc_err.LOG" ' name of the table in the above .mdb file: Global Const gcTable = "LSC" ' length of the record when PassFilter is thru with it: Global Const RecordLen = 37 2) customize each of the 6 standard routines in LSCMAKER.BAS to suit the case - comments in each routine tell what it's for - note: GENERAL.FRM is the shared main form, used by each application. FILE STRUCTURE: --------------- c:\vb\asc2mdb : contains the SHARED modules and forms GENERAL.FRM : the main form and service routines ARGV.BAS : allows changing default files used via command line GLOBALS.BAS : globals and a couple of service routines c:\vb\asc2mdb\specif : contains each SPECIFIC project project1.mak makefile for project1 project1.bas customized "framework" for project1 project2.mak project2.bas ... RECOMMENDED: You might like to STEP through the SAMPLE APPs in the debugger, to see how things connect! ===================================================== ===================================================== In case you want to read more .... COMMAND LINE CONTROL AT RUNTIME ------------------------------- You can use the command line to change: - input ASCII file - destination .mdb file - destination table - log file (for errors and run summary) - default replace mode in (exactly) that order. sample command line: myexe c:\inputarea\lscmaker.asc c:\db\lscmaker.mdb LSC c:\lsc.log 0 If any fields are specified on the command line, ALL 5 must be specified. REPLACEMODE: ------------ This is set to an integer in the range 0-5: Options that DON'T keep old records: 0 = Replace .MDB file entirely 1 - Replace the table, keep the .MDB file Options that keep old records, and add to or update them: 2 - Add the new records to the table 3 - Mass delete and add 4 - Individual lookup and replace: if NoMatch, add 5 - Individual lookup and replace: if NoMatch, log and error Modes 4 & 5 are now setup so they require indexes. Index fields are specifed in the routines "Mode345Key" and "Mode45Index". If replacemode = 5: The procedure SetupIOFields should list ONLY the fields you are UPDATING in the .MDB file. Then, other fields in the updated record will not be changed. More information on Replace Modes is in the executable under menu Edit | ReplaceMode, then push Help button. Note re Functions Mode345Key and Mode45Index -------------------------------------------- These functions should really be combined some day. You don't need to do anything with them at all unless you use ReplaceModes 3 to 5. If not using these modes, it may be better to trigger an error in these 2 routines. This might prevent you from accidently screwing things up by selecting a destructive replace mode! PASSFILTER FUNCTION: -------------------- In the simplest case, there is one line in this Function: PassFilter = True. In other cases, the code can be quite complex. It depends on how much "digesting" of the input is required for the database. This is the most flexible routine, where you do the most customizing. Each INPUT LINE from the raw ascii file is passed to this function. If PassFilter returns True, the line is posted to the database. If PassFilter returns False, the line is not posted. If you have a nicely formatted ASCII input (e.g. an ASCII file dump), PassFilter needs only one line: "PassFilter = True". Otherwise, you may need to get fancy, PassFilter is where you can do some serious reformatting. The BLK_DEL.MAK sample file shows this: - For print-formatted Currency amounts (e.g. $11,235.45): use the function SvcCurrStrip in GLOBALS.BAS: strips Currency signs from Currency fields. Leaves the length of the Currency field unchanged. Uses whatever currency you have setup in your windows profile. - change the format of the input line - ASC2MDB handles the input line as a STRING right up to the moment it's posted to the database - create local STATIC variables in this function to hold values. You might want to do this to save fields of HEADER LINES. Then add these fields to the detail lines for posting. Return FALSE when reading a header line, and TRUE only when a data record is ready for posting) - use "Like ..." operator to determine what operations to apply to the input line (i.e. depending on what kind of line it is: data, header, footer, subtotal etc.) - make sure when you are finished formatting a record, the recordlength agrees with your Global Constant "RecordLength". - IF you manipulate the record passed to the PassFilter function: The "inStart" and "inLength" fields in SetupIOFields must refer to the MODIFIED format, not the original line format! SETUPIOFIELDS, SETUPINDEXES: ---------------------------- - Dimension these arrays to include all indexes and fields you want in your .MDB file BLANK FIELDS ------------ - One issue about translating characters to data types is: what to do about blank fields in the ASCII input file. The assumption made by ASC2MDB is: if a field is blank-filled in the ASCII input field, it should be NULL in the database if a numeric data type, or ALL BLANKS in the database if a string type. The criteria is actually applied is "DB_TYPE < 10" in the routine cmdTranslate_Click in GENERAL.FRM. ODBC: ----- No effort has been made to extend this tool to ODBC at this time. This change should not be very complicated, and I'll probably find it useful to do so someday ;-). If you do it, I'd appreciate hearing from you! Enhancements? ------------- - Fields could be constructed with a WYSIWYG field editor that could read some of field and index information from the destination table. - Fields could be constructed via a simple external ASCII file. - EndUser version? With all the ReplaceModes, a lot of work would have to be done in crossvalidating settings, validating changes, User help, etc. before this could be a reasonable "end-user" tool. Replace Mode 3 is useful, but it would probably be hard to keep end users from shooting themselves in the foot ;-).